package pos.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import pos.domain.Sale;
import pos.utils.UIDGenerator;

public class JDBCSaleDAO implements ISaleDAO {
	private Connection conn;
	
	public JDBCSaleDAO () {
		this.conn = ConnectionManager.getInstance().checkOut();
	}

	@Override
	public void insertSale(Sale venta) {
		String oids = UIDGenerator.getInstance().getKey();
        PreparedStatement stmt = null;
        String sql = "INSERT INTO sales (oid, saleID, productID, price, limitDate, quantity, active) VALUES (?, ?, ?, ?, ?, ?, ?)";
        try {
            stmt = conn.prepareStatement(sql);

            stmt.setString(1, oids);
            stmt.setString(2, venta.getSaleID());
            stmt.setString(3, venta.getProductID());
            stmt.setString(4, venta.getPrice());
            stmt.setDate(5, venta.getLimitDate());
            stmt.setString(6, venta.getQuantity());
            stmt.setBoolean(7, venta.getActive());
            stmt.executeUpdate();
            
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
	}

	@Override
	public void removeSale(String saleid) {
		String sql = "UPDATE sales SET {active = false} WHERE (SaleID = ?)";
		PreparedStatement stmt = null;

		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, saleid);
			stmt.executeUpdate();

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}
		}
	}

	@Override
	public Sale selectSale(String saleid) {
		PreparedStatement stmt = null;
        ResultSet result = null;
        Sale s = null;
        String sql = "SELECT * FROM sales WHERE (saleID = ?) ";

        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, saleid);
            result = stmt.executeQuery();

            result.next();
            s = new Sale();
            s.setSaleID(saleid);
            s.setProductID(result.getString("productID"));
            s.setLimitDate(result.getDate("limitDate"));
            s.setPrice(result.getString("price"));
            s.setActive(result.getBoolean("active"));
            s.setQuantity(result.getString("quantity"));
            
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
        return s;
	}
	

	@Override
	public List<Sale> selectSalesByCategory(String productCatid) {
		PreparedStatement stmt = null;
        List<Sale> searchResults = new LinkedList<Sale>();
        ResultSet result = null;

        try {

            String sql = "SELECT * FROM sales WHERE (productCatID = ?)";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, productCatid);
            stmt.executeQuery();
            result = stmt.executeQuery();

            while (result.next()) {
                Sale temp = new Sale();
                temp.setProductID(result.getString("productID"));
                temp.setSaleID(result.getString("saleID"));
                temp.setPrice(result.getString("price"));
                temp.setQuantity(result.getString("quantity"));
                temp.setActive(result.getBoolean("active"));
                temp.setLimitDate(result.getDate("limitDate"));
                searchResults.add(temp);
            }
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            ConnectionManager.getInstance().checkIn(conn);
            try {
                if (result != null)
                    result.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }
        }
        return searchResults;
	}

	@Override
	public void updateQuantityOfSale(String newQuantity, String saleid) {
		String sql = "UPDATE sales SET {quantity = ?} WHERE (SaleID = ?)";
		PreparedStatement stmt = null;

		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(2, saleid);
			stmt.setString(1, newQuantity);
			stmt.executeUpdate();

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}
		}
	}

	public List<Sale> selectSales() {
		PreparedStatement stmt = null;
        List<Sale> searchResults = new LinkedList<Sale>();
        ResultSet result = null;

        try {

            String sql = "SELECT * FROM sales";
            stmt = conn.prepareStatement(sql);
            stmt.executeQuery();
            result = stmt.executeQuery();

            while (result.next()) {
                Sale temp = new Sale();
                temp.setProductID(result.getString("productID"));
                temp.setSaleID(result.getString("saleID"));
                temp.setPrice(result.getString("price"));
                temp.setQuantity(result.getString("quantity"));
                temp.setActive(result.getBoolean("active"));
                temp.setLimitDate(result.getDate("limitDate"));
                searchResults.add(temp);
            }
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            ConnectionManager.getInstance().checkIn(conn);
            try {
                if (result != null)
                    result.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }
        }
        return searchResults;
	}
}
